package DAO;

import com.sun.org.apache.regexp.internal.RE;
import javabean.*;

import java.sql.*;
import java.util.List;

import java.util.ArrayList;
import java.util.List;

public class MaintenanceDAO implements IMaintenanceDAO {
    @Override
    public boolean addMaintenance(Maintenance maintenance) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isSuccess = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "INSERT INTO Maintenance (TechnicianID, CustomerID, Evaluation, MaintenanceDate, MaintenanceDescription, MaintenanceStatus) VALUES (?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, maintenance.getTechnicianID());
            pstmt.setString(2, maintenance.getCustomerID());
            pstmt.setString(3, maintenance.getEvaluation());
            pstmt.setTimestamp(4, new java.sql.Timestamp(maintenance.getMaintenanceDate().getTime()));
            pstmt.setString(5, maintenance.getMaintenanceDescription());
            pstmt.setString(6, maintenance.getMaintenanceStatus());

            int rowsAffected = pstmt.executeUpdate();
            isSuccess = (rowsAffected > 0);
        } catch (SQLException e) {
            throw new Exception("Failed to add maintenance record.", e);
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }

        return isSuccess;
    }

    @Override
    public boolean addUnassignedMaintenance(Maintenance maintenance) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isSuccess = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "INSERT INTO UnassignedMaintenance (CustomerID, MaintenanceDate, MaintenanceDescription) VALUES (?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, maintenance.getCustomerID());
            pstmt.setTimestamp(2, new java.sql.Timestamp(maintenance.getMaintenanceDate().getTime()));
            pstmt.setString(3, maintenance.getMaintenanceDescription());

            int rowsAffected = pstmt.executeUpdate();
            isSuccess = (rowsAffected > 0);
        } catch (SQLException e) {
            throw new Exception("Failed to add unassigned maintenance record.", e);
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }

        return isSuccess;
    }

    @Override
    public List<Maintenance> getAllMaintenance() throws Exception {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;

        conn = ConnectManager.getConnection();
        String sql = "SELECT * FROM Maintenance";
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        List<Maintenance> maintenanceList = new ArrayList<Maintenance>();
        while (rs.next()) {
            Maintenance maintenance = new Maintenance();
            maintenance.setMaintenanceID(rs.getInt("MaintenanceID"));
            maintenance.setTechnicianID(rs.getString("TechnicianID"));
            maintenance.setCustomerID(rs.getString("CustomerID"));
            maintenance.setEvaluation(rs.getString("Evaluation"));
            maintenance.setMaintenanceDate(rs.getTimestamp("MaintenanceDate"));
            maintenance.setMaintenanceDescription(rs.getString("MaintenanceDescription"));
            maintenance.setMaintenanceStatus(rs.getString("MaintenanceStatus"));
            maintenanceList.add(maintenance);
        }
        ConnectManager.closeConnection(conn, pstmt, rs);
        return maintenanceList;
    }

    @Override
    public List<Maintenance> getUnassignedMaintenance() throws Exception {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;

        conn = ConnectManager.getConnection();
        String sql = "SELECT * FROM UnassignedMaintenance";
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        List<Maintenance> maintenanceList = new ArrayList<Maintenance>();
        while (rs.next()) {
            Maintenance maintenance = new Maintenance();
            maintenance.setMaintenanceID(rs.getInt("MaintenanceID"));
            maintenance.setCustomerID(rs.getString("CustomerID"));
            maintenance.setMaintenanceDate(rs.getTimestamp("MaintenanceDate"));
            maintenance.setMaintenanceDescription(rs.getString("MaintenanceDescription"));
            maintenance.setMaintenanceStatus("未处理");
            maintenanceList.add(maintenance);
        }
        ConnectManager.closeConnection(conn, pstmt, rs);
        return maintenanceList;
    }

    @Override
    public List<Maintenance> getMaintenanceByCID(String CustomerID) throws Exception {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;

        conn = ConnectManager.getConnection();
        String sql = "SELECT * FROM Maintenance WHERE CustomerID = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, CustomerID);
        rs = pstmt.executeQuery();
        List<Maintenance> maintenanceList = new ArrayList<Maintenance>();
        while (rs.next()) {
            Maintenance maintenance = new Maintenance();
            maintenance.setMaintenanceID(rs.getInt("MaintenanceID"));
            maintenance.setTechnicianID(rs.getString("TechnicianID"));
            maintenance.setCustomerID(rs.getString("CustomerID"));
            maintenance.setEvaluation(rs.getString("Evaluation"));
            maintenance.setMaintenanceDate(rs.getTimestamp("MaintenanceDate"));
            maintenance.setMaintenanceDescription(rs.getString("MaintenanceDescription"));
            maintenance.setMaintenanceStatus(rs.getString("MaintenanceStatus"));
            maintenanceList.add(maintenance);
        }
        ConnectManager.closeConnection(conn, pstmt, rs);
        return maintenanceList;
    }

    @Override
    public List<Maintenance> addUnassignedMaintenanceByCID(String CustomerID) throws Exception {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;

        conn = ConnectManager.getConnection();
        String sql = "SELECT * FROM UnassignedMaintenance WHERE CustomerID = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, CustomerID);
        rs = pstmt.executeQuery();
        List<Maintenance> maintenanceList = new ArrayList<Maintenance>();
        while (rs.next()) {
            Maintenance maintenance = new Maintenance();
            maintenance.setMaintenanceID(rs.getInt("MaintenanceID"));
            maintenance.setCustomerID(rs.getString("CustomerID"));
            maintenance.setMaintenanceDate(rs.getTimestamp("MaintenanceDate"));
            maintenance.setMaintenanceDescription(rs.getString("MaintenanceDescription"));
            maintenance.setMaintenanceStatus("未处理");
            maintenanceList.add(maintenance);
        }
        ConnectManager.closeConnection(conn, pstmt, rs);
        return maintenanceList;
    }

    @Override
    public List<Maintenance> getMaintenanceByTID(String technicianID) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Maintenance> maintenanceList = new ArrayList<>();

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Maintenance WHERE TechnicianID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, technicianID);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Maintenance maintenance = new Maintenance();
                maintenance.setMaintenanceID(rs.getInt("MaintenanceID"));
                maintenance.setTechnicianID(rs.getString("TechnicianID"));
                maintenance.setCustomerID(rs.getString("CustomerID"));
                maintenance.setEvaluation(rs.getString("Evaluation"));
                maintenance.setMaintenanceDate(rs.getTimestamp("MaintenanceDate"));
                maintenance.setMaintenanceDescription(rs.getString("MaintenanceDescription"));
                maintenance.setMaintenanceStatus(rs.getString("MaintenanceStatus"));

                maintenanceList.add(maintenance);
            }
        } catch (SQLException e) {
            throw new Exception("Failed to search maintenance records by technician ID.", e);
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }

        return maintenanceList;
    }

    @Override
    public boolean distributionMaintenance(Maintenance maintenance, String TechnicianID) throws Exception {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean isSuccess = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "CALL AssignMaintenance(?, ?)";
            cstmt = conn.prepareCall(sql);
            cstmt.setInt(1, maintenance.getMaintenanceID());
            cstmt.setString(2, TechnicianID);

            int rowsAffected = cstmt.executeUpdate();
            isSuccess = (rowsAffected > 0);
        } catch (SQLException e) {
            throw new Exception("Failed to process maintenance record using stored procedure.", e);
        } finally {
            ConnectManager.closeConnection(conn, cstmt, null);
        }

        return isSuccess;
    }


    @Override
    public boolean updateMaintenance(Maintenance maintenance) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isSuccess = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "UPDATE Maintenance SET TechnicianID = ?, CustomerID = ?, Evaluation = ?, MaintenanceDate = ?, MaintenanceDescription = ?, MaintenanceStatus = ? WHERE MaintenanceID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, maintenance.getTechnicianID());
            pstmt.setString(2, maintenance.getCustomerID());
            pstmt.setString(3, maintenance.getEvaluation());
            pstmt.setTimestamp(4, new java.sql.Timestamp(maintenance.getMaintenanceDate().getTime()));
            pstmt.setString(5, maintenance.getMaintenanceDescription());
            pstmt.setString(6, maintenance.getMaintenanceStatus());
            pstmt.setInt(7, maintenance.getMaintenanceID());

            int rowsAffected = pstmt.executeUpdate();
            isSuccess = (rowsAffected > 0);
        } catch (SQLException e) {
            throw new Exception("Failed to update maintenance record.", e);
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }

        return isSuccess;
    }

    @Override
    public boolean deleteMaintenance(Maintenance maintenance) throws Exception {
        return false;
    }

    @Override
    public Maintenance getMaintenanceByID(int maintenanceID) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Maintenance maintenance = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Maintenance WHERE MaintenanceID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, maintenanceID);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                maintenance = new Maintenance();
                maintenance.setMaintenanceID(rs.getInt("MaintenanceID"));
                maintenance.setTechnicianID(rs.getString("TechnicianID"));
                maintenance.setCustomerID(rs.getString("CustomerID"));
                maintenance.setEvaluation(rs.getString("Evaluation"));
                maintenance.setMaintenanceDate(rs.getTimestamp("MaintenanceDate"));
                maintenance.setMaintenanceDescription(rs.getString("MaintenanceDescription"));
                maintenance.setMaintenanceStatus(rs.getString("MaintenanceStatus"));
            }
        } catch (SQLException e) {
            throw new Exception("Failed to search maintenance record by ID.", e);
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }

        return maintenance;
    }

    public List<Maintenance> getPendingMaintance(String technicianID) throws Exception {//获取TechnicianID对应的待处理状态的维修单
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Maintenance> maintenanceList = new ArrayList<>();

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Maintenance WHERE TechnicianID = ? AND MaintenanceStatus = '待处理'";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, technicianID);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Maintenance maintenance = new Maintenance();
                maintenance.setMaintenanceID(rs.getInt("MaintenanceID"));
                maintenance.setTechnicianID(rs.getString("TechnicianID"));
                maintenance.setCustomerID(rs.getString("CustomerID"));
                maintenance.setEvaluation(rs.getString("Evaluation"));
                maintenance.setMaintenanceDate(rs.getTimestamp("MaintenanceDate"));
                maintenance.setMaintenanceDescription(rs.getString("MaintenanceDescription"));
                maintenance.setMaintenanceStatus(rs.getString("MaintenanceStatus"));

                maintenanceList.add(maintenance);
            }
        } catch (SQLException e) {
            throw new Exception("Failed to search maintenance records by technician ID.", e);
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }

        return maintenanceList;
    }

    public List<Maintenance> getInProcessMaintenance(String technicianID) throws Exception {//获取TechnicianID对应的待处理状态的维修单
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Maintenance> maintenanceList = new ArrayList<>();

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Maintenance WHERE TechnicianID = ? AND MaintenanceStatus = '处理中'";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, technicianID);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Maintenance maintenance = new Maintenance();
                maintenance.setMaintenanceID(rs.getInt("MaintenanceID"));
                maintenance.setTechnicianID(rs.getString("TechnicianID"));
                maintenance.setCustomerID(rs.getString("CustomerID"));
                maintenance.setEvaluation(rs.getString("Evaluation"));
                maintenance.setMaintenanceDate(rs.getTimestamp("MaintenanceDate"));
                maintenance.setMaintenanceDescription(rs.getString("MaintenanceDescription"));
                maintenance.setMaintenanceStatus(rs.getString("MaintenanceStatus"));

                maintenanceList.add(maintenance);
            }
        } catch (SQLException e) {
            throw new Exception("Failed to search maintenance records by technician ID.", e);
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }

        return maintenanceList;
    }

    public boolean completeMaintenance(String MaintenanceID) throws Exception {
        //将MaintenanceID对应的维修单的状态改为已完成
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isSuccess = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "UPDATE Maintenance SET MaintenanceStatus = '已完成' WHERE MaintenanceID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, MaintenanceID);

            int rowsAffected = pstmt.executeUpdate();
            isSuccess = (rowsAffected > 0);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return isSuccess;
    }

    public boolean inProcessMaintenance(String MaintenanceID) throws Exception {
        //将MaintenanceID对应的维修单的状态改为处理中
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isSuccess = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "UPDATE Maintenance SET MaintenanceStatus = '处理中' WHERE MaintenanceID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, MaintenanceID);

            int rowsAffected = pstmt.executeUpdate();
            isSuccess = (rowsAffected > 0);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return isSuccess;
    }

    @Override
    public boolean evaluateMaintenance(int MaintenanceID, String Evaluation) throws Exception{
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean isSuccess = false;

        try {
            conn = ConnectManager.getConnection();
            String sql = "UPDATE Maintenance SET Evaluation = ? WHERE MaintenanceID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, Evaluation);
            pstmt.setInt(2, MaintenanceID);

            int rowsAffected = pstmt.executeUpdate();
            isSuccess = (rowsAffected > 0);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return isSuccess;
    }
}
